package org.bluedream.comm.utils.excel;

import lombok.Getter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bluedream.comm.utils.EmptyUtil;
import org.bluedream.comm.utils.ReflexUtil;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


/**
 * @ClassName ExcelImport
 * @Description TODO
 * @Author foxsand
 * @Data 2021-08-26 10:04
 * @Version
 */
public class ExcelImport extends BaseExcel implements Serializable {
    private static final long serialVersionUID = 7085381286559343720L;
    @Getter
    private Map<Integer , String> header = new LinkedHashMap<>();


    public void setHeader(Sheet sheet , int headerRowNum , Class<?> clazz){
        //获取实体类注解属性
        setAnnotationAnalyses(clazz);
        Row row = sheet.getRow(headerRowNum);
        int firstCellIndex = row.getFirstCellNum();
        int lastCellIndex = row.getLastCellNum();

        for (ExcelAnnotationAnalysis annotationAnalysis:this.annotationAnalyses
             ) {
            String excelFieldName = annotationAnalysis.getExcelFieldName();
            ExcelType excelType = annotationAnalysis.getExcelType();
            if (excelType == ExcelType.ALL || excelType == ExcelType.IMPORT){
                boolean isRequired = annotationAnalysis.isRequired();
                boolean flag = false;
                for (int i = firstCellIndex; i < lastCellIndex; i++) {
                    String cellValue = String.valueOf(getCellValue(row , i));
                    if (EmptyUtil.isEmpty(cellValue)){
                        throw new ExcelException("ExcelException:导入的excel文件，标题行存在空值，column num = " + i);
                    }
                    if (EmptyUtil.isNoEmpty(cellValue) && cellValue.equals(excelFieldName)){
                        flag = true;
                        this.header.put(i , cellValue);
                        break;
                    }
                }
                if (isRequired && flag != true){
                    throw new ExcelException("ExcelException:导入的excel文件缺少必填字段：" + excelFieldName);
                }
            }
        }
        if (EmptyUtil.isEmpty(this.header)){
            throw new ExcelException("ExcelException:没有可用于导入的数据，请确认！");
        }
    }

    private Object getCellValue(Row row , int column){
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null){
                if (cell.getCellType() == 0){
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                        val = DateUtil.getJavaDate((Double) val);
                    }else {
                        val = new DecimalFormat("0").format(val);
                    }
                }else if (cell.getCellType() == 1){
                    val = cell.getStringCellValue();
                }else if (cell.getCellType() == 2){
                    try {
                        val = cell.getStringCellValue();
                    }catch (Exception e1){
                        FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                        evaluator.evaluateFormulaCell(cell);
                        CellValue cellValue = evaluator.evaluate(cell);
                        switch(cellValue.getCellType()) {
                            case 0:
                                val = cellValue.getNumberValue();
                                break;
                            case 1:
                                val = cellValue.getStringValue();
                                break;
                            case 2:
                            case 3:
                            default:
                                val = cell.getCellFormula();
                                break;
                            case 4:
                                val = cellValue.getBooleanValue();
                                break;
                            case 5:
                                val = ErrorEval.getText(cellValue.getErrorValue());
                        }
                    }
                }else if (cell.getCellType() == 4){
                    val = cell.getBooleanCellValue();
                }else if (cell.getCellType() == 5){
                    val = cell.getErrorCellValue();
                }
            }
            return val;
        }catch (Exception e){
            return val;
        }
    }

    /**
     * 读取excel中的工作表
     * @param fileName
     * @param in
     */
    public void setWorkbook(String fileName , InputStream in){
        try {
            if (fileName.endsWith("xls")){
                this.workbook = new HSSFWorkbook(in);
            }else if (fileName.endsWith("xlsx")){
                this.workbook = new XSSFWorkbook(in);
            }else {
                throw new ExcelException("ExcelException:导入的文档格式不正确，必须是xls或者xlsx文件!");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 读取excel中指定的sheet
     * @param wb
     * @param sheetIndexOrName 指定的sheet索引，当为数字时，按sheet序号读取sheet，sheet下标从0开始
     */
    public void setSheet(Workbook wb , Object sheetIndexOrName){
        if (sheetIndexOrName instanceof Integer || sheetIndexOrName instanceof Long){
            Long index = ((Number) sheetIndexOrName).longValue();
            this.sheet = this.workbook.getSheetAt(index.intValue());
        }else {
            this.sheet = this.workbook.getSheet(String.valueOf(sheetIndexOrName));
        }

        if (this.sheet == null){
            throw new ExcelException("ExcelException：没有找到" + sheetIndexOrName + "工作表！");
        }
    }


    private  <E>E getRowData(Row row , Class<E> clazz){
        try {
            E entity = clazz.newInstance();
            for (Map.Entry<Integer , String> map:this.header.entrySet()
                 ) {
                String excelFieldName = map.getValue();
                Integer columnIndex = map.getKey();
                for (ExcelAnnotationAnalysis annotationAnalysis:this.annotationAnalyses
                     ) {
                    if (excelFieldName.equals(annotationAnalysis.getExcelFieldName())){
                        Object val = getCellValue(row , columnIndex);
                        // 根据 对象和属性名通过反射 调用上面的方法获取 Field对象
                        Field field = ReflexUtil.getDeclaredField(entity, annotationAnalysis.getFieldName());
                        String s1 = field.getType().getSimpleName();
                        if (s1.equals("BigDecimal")){
                            val = new BigDecimal((String) val);
                        }else if (s1.equals("Integer")){
                            val = Integer.valueOf((String) val);
                        }
                        ReflexUtil.setFieldValue(entity , annotationAnalysis.getFieldName() , val);
                        break;
                    }
                }
            }
            return entity;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public <E>List<E> readExcel2List(MultipartFile file , Object sheetIndexOrName , Class<E> clazz , int headerRowNum){
        try {
            setWorkbook(file.getOriginalFilename() , file.getInputStream());
            return getDataList(sheetIndexOrName, clazz, headerRowNum);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public List<String> readExcel2List(MultipartFile file , Object sheetIndexOrName , int headerRowNum){
        try {
            setWorkbook(file.getOriginalFilename() , file.getInputStream());
            return getDataList(sheetIndexOrName, headerRowNum);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    private List<String> getDataList(Object sheetIndexOrName, int headerRowNum) {
        try {
            setSheet(this.workbook , sheetIndexOrName);
            int dataRowNumIndex = headerRowNum + 1;
            // 最后一行 行号
            int dataLastRowNum = this.sheet.getLastRowNum();
            if (dataLastRowNum < dataRowNumIndex){
                throw new ExcelException("ExcelException:没有可导入的数据！");
            }

            List<String> list = new ArrayList<>();
            for (int i = dataRowNumIndex; i <= dataLastRowNum; i++) {
                list.add((String) getCellValue(this.sheet.getRow(i) , 0));
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public <E>List<E> readExcel2List(File file , Object sheetIndexOrName , Class<E> clazz , int headerRowNum){
        try {
            setWorkbook(file.getName() , new FileInputStream(file));
            return getDataList(sheetIndexOrName, clazz, headerRowNum);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    private <E>List<E> getDataList(Object sheetIndexOrName, Class<E> clazz, int headerRowNum) {
        try {
            setSheet(this.workbook , sheetIndexOrName);
            setHeader(this.sheet , headerRowNum , clazz);
            int dataRowNumIndex = headerRowNum + 1;
            // 最后一行 行号
            int dataLastRowNum = this.sheet.getLastRowNum();
            if (dataLastRowNum < dataRowNumIndex){
                throw new ExcelException("ExcelException:没有可导入的数据！");
            }

            List<E> list = new ArrayList<>();
            for (int i = dataRowNumIndex; i <= dataLastRowNum; i++) {
                E entity = getRowData(this.sheet.getRow(i) , clazz);
                list.add(entity);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取实体类中的注解
     * @param clazz
     */
    private void setAnnotationAnalyses(Class<?> clazz){
        try {
            this.annotationAnalyses = ExcelUtil.getAnnotationAnalyses(clazz , ExcelType.IMPORT);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
